package com.example.demo.mapper;

import com.example.demo.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @Select("select * from user_info")
    @ResultMap("mapping")
    List<UserInfo> getUserInfoAll();

    @Select("select * from user_info where delete_flag = #{deleteFlag}")
    List<UserInfo> getUserInfoByDeleteFlag(Integer deleteFlag);

    @Select("select * from user_info where gender = #{gender} and delete_flag = #{deleteFlag}")
    List<UserInfo> getUserInfoByDeleteFlagAndGender(Integer gender, Integer deleteFlag);

    @Select("select * from user_info where id = #{id}")
    List<UserInfo> getUserInfoById(Integer id);

    @Select("select * from user_info where id = #{id}")
    UserInfo getUserInfoById2(Integer id);

    @Insert("insert into user_info (username, password, age, gender, phone, delete_flag) " +
            "values(#{username}, #{password}, #{age}, #{gender}, #{phone}, #{deleteFlag})")
    Integer insert(UserInfo userInfo);

    @Insert("insert into user_info (username, password, age, gender, delete_flag) " +
            "values(#{userInfo.username}, #{userInfo.password}, #{userInfo.age}, " +
            "#{userInfo.gender}, #{userInfo.deleteFlag})")
    Integer insert2(@Param("userInfo") UserInfo userInfo);

    @Delete("delete from user_info where id = #{id}")
    Integer delete(UserInfo userInfo);

    @Update("update user_info set username = #{username} " +
            "where age = #{age} and password = #{password}")
    Integer update(UserInfo userInfo);

    /**
     * 起别名
     * @return
     */
    @Select("select id, username, password, age,gender, phone, " +
            "delete_flag as deleteFlag, create_time as createTime, update_time as updateTime from user_info")
    List<UserInfo> getUserInfoAll2();

    /**
     * 手动指定映射
     */
    @Select("select id, username, password, age,gender, phone, " +
            "delete_flag, create_time, update_time from user_info")
    @Results(id = "mapping", value = {
            @Result(column = "delete_flag", property = "deleteFlag"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    List<UserInfo> getUserInfoAll3();

    /**
     * 使用配置文件
     */
    @Select("select id, username, password, age,gender, phone, " +
            "delete_flag, create_time, update_time from user_info")
    List<UserInfo> getUserInfoAll4();

    /**
     * SQL注入问题
     * @param username
     * @return
     */
    @Select("select * from user_info where username = #{username}")
    List<UserInfo> getUserInfoByUsername(String username);

    @Select("select * from user_info where username = '${username}'")
    List<UserInfo> getUserInfoByUsername2(String username);

    /**
     * $符号的应用场景
     * @param order
     * @return
     */
    @Select("select id, username, password, age,gender, phone, " +
            "delete_flag, create_time, update_time from user_info " +
            "order by id ${order}")
    List<UserInfo> getSortedUserInfoAll(String order);

    /**
     * 模糊查询
     */
    @Select("select id, username, password, age,gender, phone, " +
            "delete_flag, create_time, update_time from user_info " +
            "where username like CONCAT('%', #{username} ,'%')")
    List<UserInfo> getUserInfoByLike(String username);
}
